package com.hui.excel.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel工具
 * Created by LiaoYunHui on 2017/10/12/012.
 */
public class ExcelUtil {
    public static String DEFAULT_DATE_PATTERN = "yyyy-MM-dd";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 15;

    /**
     * 导出Excel 97(.xls)格式 ，少量数据
     *
     * @param headMap     属性-列名
     * @param jsonArray   数据集
     * @param datePattern 日期格式，null则用默认日期格式
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public static void exportExcel(Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook.createInformationProperties();
        SummaryInformation si = workbook.getSummaryInformation();
        si.setCreateDateTime(new Date());
        // 列头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        // 单元格样式，字体设置
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont cellFont = workbook.createFont();
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        HSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
            String fieldName = iter.next();
            properties[ii] = fieldName;
            headers[ii] = fieldName;
            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                HSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =0
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);
                }
                rowIndex = 1;//数据内容从 rowIndex=1开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            HSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell newCell = dataRow.createCell(i);

                Object o = jo.get(properties[i]);
                String cellValue = "";
                if (o == null) cellValue = "";
                else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                else cellValue = o.toString();

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param headMap     属性-列头
     * @param jsonArray   数据集
     * @param datePattern 日期格式，传null值则默认
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public static void exportExcelX(Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        /*通过设置SXXFWorkbook的构造参数，可以设置每次在内存中保持的行数，当达到这个值的时候，那么会把这些数据flush到磁盘上，这样就不会出现内存不够的情况*/
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//内存中缓存1000条数据
        workbook.setCompressTempFiles(true);//临时文件进行压缩，建议不要true，否则会影响导出时间
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);//列头字体大小
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font cellFont = workbook.createFont();
        cellStyle.setFont(cellFont);
        // 生成表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
            String fieldName = iter.next();
            //设置列头名
            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);
            //列宽处理
            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
//            sheet.trackAllColumnsForAutoSizing();//打开自动设置列宽
//            sheet.autoSizeColumn(ii);//自动设置列宽，效果不好
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                SXSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =0
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);
                }
                rowIndex = 1;//数据内容从 rowIndex=1开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0, j = properties.length; i < j; i++) {
                SXSSFCell newCell = dataRow.createCell(i);
                //数据显示处理
                Object o = jo.get(properties[i]);
                String cellValue = "";
                if (o == null) cellValue = ""; //元素为null，单元格显示的值为""
                else if (o instanceof Date) {//元素为日期类型，通过规则显示日期
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else if (o instanceof Float || o instanceof Double) {//元素为小数类型
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                } else {//其它类型
                    cellValue = o.toString();
                }
                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
                //列宽处理
                int bytes = cellValue.getBytes().length;
                arrColWidth[i] = bytes < minBytes ? minBytes : bytes;
                sheet.setColumnWidth(i, arrColWidth[i] * 256);
            }
            rowIndex++;
        }
        try {
            workbook.write(out);//文件写人流中
            workbook.close();//关闭工作薄流，释放内存资源
            workbook.dispose();//处理磁盘上支持此工作簿的临时文件，释放磁盘资源
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Web 导出excel
     *
     * @param title    文件名
     * @param headMap  列头
     * @param ja       数据集
     * @param response 响应
     */
    public static void downloadExcelFile(String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcelX(headMap, ja, null, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取Excel数据内容
     */
    public static List<Map<Integer, String>> readExcelContentToList(InputStream inputStream) throws IOException {
        //从流中读取工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        //获取sheet
        XSSFSheet sheet1 = xssfWorkbook.getSheet("Sheet1");
        int lastRowNum = sheet1.getLastRowNum();
        //循环读取行
        List<Map<Integer, String>> mapList = new LinkedList<>();
        for (int i = 1; i <= lastRowNum; i++) {
            Map<Integer, String> map = new HashMap<>();
            XSSFRow row = sheet1.getRow(i);
            int lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                String cellValue = "";
                XSSFCell cell = row.getCell(j);
                CellType cellTypeEnum = cell.getCellTypeEnum();
                switch (cellTypeEnum) {
                    case STRING://字符串类型
                        cellValue = cell.getStringCellValue().trim();
                        cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
                        break;
                    case NUMERIC://数值类型
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
                            cellValue = cell.getDateCellValue().toString();
                        } else {  //否
                            cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                        }
                        break;
                    case BOOLEAN://布尔类型
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                    default: //其它类型，取空串吧
                        cellValue = "";
                        break;
                }
                map.put(j, cellValue);
                mapList.add(map);
            }
        }
        return mapList;
    }


//    /**
//     * 调试用
//     *
//     * @param args
//     * @throws IOException
//     */
//    public static void main(String[] args) throws IOException {
//        int count = 0;
//        JSONArray ja = new JSONArray();
//        for (int i = 0; i < 100; i++) {
//            Student s = new Student();
//            s.setName("POI" + i);
//            s.setAge(i);
//            s.setBirthday(new Date());
//            s.setHeight(i);
//            s.setWeight(i);
//            s.setSex(i / 2 != 0);
//            ja.add(s);
//            count++;
//        }
//        Map<String, String> headMap = new LinkedHashMap<String, String>();
//        headMap.put("name", "姓名");
//        headMap.put("age", "年龄");
//        headMap.put("birthday", "生日");
//        headMap.put("height", "身高");
//        headMap.put("weight", "体重");
//        headMap.put("sex", "性别");
//
//        OutputStream outXls = new FileOutputStream("/home/hui/桌面/test_excel.xls");
//        System.out.println("正在导出xls....");
//        Date d = new Date();
//        ExcelUtil.exportExcel(headMap, ja, null, 17, outXls);
//        System.out.println("共" + count + "条数据,执行" + (new Date().getTime() - d.getTime()) + "ms");
//        outXls.close();
//
////        OutputStream outXlsx = new FileOutputStream("/home/hui/桌面/test_excel.xlsx");
////        System.out.println("正在导出xlsx....");
////        Date d2 = new Date();
////        ExcelUtil.exportExcelX(headMap, ja, null, 0, outXlsx);
////        System.out.println("共" + count + "条数据,执行" + (new Date().getTime() - d2.getTime()) + "ms");
////        outXlsx.close();
//
//    }
//}
//
//class Student {
//    private String name;
//    private int age;
//    private Date birthday;
//    private float height;
//    private double weight;
//    private boolean sex;
//
//    public String getName() {
//        return name;
//    }
//
//    public void setName(String name) {
//        this.name = name;
//    }
//
//    public Integer getAge() {
//        return age;
//    }
//
//    public Date getBirthday() {
//        return birthday;
//    }
//
//    public void setBirthday(Date birthday) {
//        this.birthday = birthday;
//    }
//
//    public float getHeight() {
//        return height;
//    }
//
//    public void setHeight(float height) {
//        this.height = height;
//    }
//
//    public double getWeight() {
//        return weight;
//    }
//
//    public void setWeight(double weight) {
//        this.weight = weight;
//    }
//
//    public boolean isSex() {
//        return sex;
//    }
//
//    public void setSex(boolean sex) {
//        this.sex = sex;
//    }
//
//    public void setAge(Integer age) {
//        this.age = age;
//    }
}